﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Runtime.InteropServices;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;

namespace FIS.iDET
{
    /// <summary>
    /// Class providing methods to work with data importing from and exporting to Microsoft Excel
    /// </summary>
    public class ExcelService
    {
        /// <summary>
        /// The excel app (COM)
        /// This costs memory since it loads one new excel app to one instance of this class.
        /// Initialize this class with care!
        /// ALWAYS remember to call QuitExelApp() to quit the excel app after using
        /// I don't make this static since it's may cause conflict when
        /// many workbooks are opened at the same time
        /// </summary>
        _Application excelApp = new Microsoft.Office.Interop.Excel.Application();
        
        // excel file full path, this is the excel file this class will work on
        string excelFileName;
        public int sheetsCount;
        Boolean saved = true;

        public ExcelService()
        {
            // init excel app
            // hide from UI
            excelApp.Visible = false;
            // no alert boxes
            excelApp.DisplayAlerts = false;
        }

        /// <summary>
        /// initialize new excel service for an excel file
        /// </summary>
        /// <param name="excelFullFilePath">full path to excel file for this excel service to work on</param>
        public ExcelService(string excelFileName)
        {
            this.excelFileName = excelFileName;
            
            // init excel app
            // hide from UI

            excelApp.Visible = false;

            // no alert boxes

            excelApp.DisplayAlerts = false;        
        }

        /// <summary>
        /// check if current excel file exists
        /// </summary>
        public Boolean FileExists
        {
            get
            {
                if (String.IsNullOrEmpty(excelFileName))
                    return false;
                else
                    return File.Exists(excelFileName);
            }
        }

        public Boolean Saved
        {
            get
            {
                return saved;
            }
            set
            {
                saved = value;
            }
        }

        public string FileName
        {
            get
            {
                return excelFileName;
            }
            set
            {
                excelFileName = value;
            }
        }

        public void SaveAsAutoSave(DataGridView dataGridView) //H.NH handle autosave corruption
        {
            if (dataGridView.ColumnCount == 0)
                return;

            _Workbook workBook;

            try
            {
                // creating new WorkBook within Excel application
                workBook = excelApp.Workbooks.Add(Type.Missing);
            

            // get new Excelsheet in workbook
            _Worksheet workSheet = workBook.Sheets["Sheet1"] as _Worksheet; //TODO delete

            // store its reference to worksheet
            workSheet = workBook.ActiveSheet as _Worksheet;

            // store header part in Excel
            for (int i = 1; i < dataGridView.Columns.Count + 1; i++)
                workSheet.Cells[1, i] = dataGridView.Columns[i - 1].HeaderText;

            // store data to temp array
            object[,] values = new object[dataGridView.Rows.Count, dataGridView.Columns.Count];

            for (int i = 0; i < dataGridView.Rows.Count; i++)
                for (int j = 0; j < dataGridView.Columns.Count; j++)
                    if (dataGridView.Rows[i].Cells[j].Value != null)
                        values[i, j] = dataGridView.Rows[i].Cells[j].Value.ToString();

            // store Each row and column value to excel sheet
            string lastCell = GetLastCellName(dataGridView.Columns.Count, dataGridView.Rows.Count);
            if (GlobalConstants.TemplatePageCol != 0) // multiline mode
                workSheet.get_Range("A2", lastCell).NumberFormat = "@"; // all data save as string !
            workSheet.get_Range("A2", lastCell).Value2 = values;

            }
            catch (Exception e)
            {
                // debug
                // MessageBox.Show("Cannot save file, please ensure that Excel is not opening else where.\n" + e.Message, "Save file error");
                return;
            }

            try
            {
                // delete destination file, if any
                if (File.Exists(excelFileName))
                    File.Delete(excelFileName);

                // save the workbook
                workBook.SaveAs(excelFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing);

                // set curent status of excel file to "just saved"
                saved = true;
            }
            catch (Exception e)
            {
                // debug
                //MessageBox.Show("File cannot be saved, please check file permissions.\n" + e.Message, "Save file error");
            }
            finally
            {
                // close workbook and worksheet
                workBook.Close();
                workBook = null;
                GC.Collect();
            }
        }

        public void SaveAs(DataGridView dataGridView)
        {
            if (dataGridView.ColumnCount == 0)
                return;

            _Workbook workBook;

            try
            {
                // creating new WorkBook within Excel application
                workBook = excelApp.Workbooks.Add(Type.Missing);
            }
            catch (Exception e)
            {
                // debug
                MessageBox.Show("Cannot save file, please ensure that Excel is not opening else where.\n" + e.Message, "Save file error");
                return;
            }
           
            // get new Excelsheet in workbook
            _Worksheet workSheet = workBook.Sheets["Sheet1"] as _Worksheet; //TODO delete

            // store its reference to worksheet
            workSheet = workBook.ActiveSheet as _Worksheet;

            // store header part in Excel
            for (int i = 1; i < dataGridView.Columns.Count + 1; i++)
                workSheet.Cells[1, i] = dataGridView.Columns[i - 1].HeaderText;
            
            // store data to temp array
            object[,] values = new object[dataGridView.Rows.Count, dataGridView.Columns.Count];

            for (int i = 0; i < dataGridView.Rows.Count; i++)
                for (int j = 0; j < dataGridView.Columns.Count; j++)
                    if (dataGridView.Rows[i].Cells[j].Value != null)
                        values[i, j] = dataGridView.Rows[i].Cells[j].Value.ToString();

            // store Each row and column value to excel sheet
            string lastCell = GetLastCellName(dataGridView.Columns.Count, dataGridView.Rows.Count);
            if (GlobalConstants.TemplatePageCol != 0 ) // multiline mode
                workSheet.get_Range("A2", lastCell).NumberFormat = "@"; // all data save as string !
            workSheet.get_Range("A2", lastCell).Value2 = values;

            try
            {
                // delete destination file, if any
                if (File.Exists(excelFileName))
                    File.Delete(excelFileName);

                // save the workbook
                workBook.SaveAs(excelFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing);                
                
                // set curent status of excel file to "just saved"
                saved = true;
            }
            catch ( Exception e)
            {
                // debug
                MessageBox.Show("File cannot be saved, please check file permissions.\n" + e.Message, "Save file error");
            }
            finally
            {
                // close workbook and worksheet
                workBook.Close();
                workSheet = null;
                workBook = null;
                GC.Collect();
            }
        }

        public void SaveComapreResultAs(List<PageCompareInfo> listAllPagesCompare, int numsheets, ref List<ExcelSheet> listExcelSheetsResult)
        {
            _Workbook workBook;
            try
            {
                // creating new WorkBook within Excel application
                workBook = excelApp.Workbooks.Add(Type.Missing);
            }
            catch (Exception e)
            {
                // debug
                MessageBox.Show("Cannot save file, please ensure that Excel is not opening else where.\n"+e.Message, "Save file error");
                return;
            }

            for (int i = numsheets; i >= GlobalVariables.startSheet; i--)
            {
                // get new Excelsheet in workbook
                string sheetName = string.Format("Page{0}", i);
                Sheets xlSheets = workBook.Sheets as Sheets; //TODO delete                
                //add new sheet
                //_Worksheet newSheet = (_Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);
                _Worksheet newSheet = (_Worksheet)xlSheets.Add();
                newSheet.Name = sheetName;

                // store header part in Excel
                for (int j = 0; j < listAllPagesCompare[i].listColumnCompare.Count; j++)
                    newSheet.Cells[1, j + 1] = listAllPagesCompare[i].listColumnCompare[j].fieldName;

                // add page number
                // DANGEROUS CODE HERE !!!
                newSheet.Cells[listAllPagesCompare[i].listColumnCompare.Count + 1] = GlobalConstants.ExtendColumnName;

                // identify last cell
                int id = listAllPagesCompare[i].listColumnCompare.Count - 1;
                int lastCellColumn = listAllPagesCompare[i].listColumnCompare[id].column + GlobalConstants.ExtendColumn;
                int lastCellRow = listAllPagesCompare[i].endRow;

                // store data to excel                    
                string lastCell = GetLastCellName(lastCellColumn, lastCellRow);
                newSheet.get_Range("A2", lastCell).NumberFormat = "@";
                newSheet.get_Range("A2", lastCell).Value2 = listExcelSheetsResult[i].value;
            }

            // delete Sheet1, Sheet2, Sheet3
            for (int i = 0; i < 3; i++)
            {
                _Worksheet sheet = workBook.Worksheets[numsheets - GlobalVariables.startSheet + 2] as _Worksheet;
                sheet.Delete();
            }
            try
            {
                // delete destination file, if any
                if (File.Exists(excelFileName))
                    File.Delete(excelFileName);

                // save the workbook
                workBook.SaveAs(excelFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing);

                // set curent status of excel file to "just saved"
                saved = true;
            }
            catch (Exception e)
            {
                // debug
                MessageBox.Show("File cannot be saved, please check file permissions.\n" + e.Message, "Save file error");
            }
            finally
            {
                // close workbook and worksheet
                workBook.Close();
                Marshal.ReleaseComObject(workBook);
                workBook = null;
                GC.Collect();
            }
        }

        public void SaveComapreResultAsOneSheet(List<PageCompareInfo> listAllPagesCompare, int numsheets, ref List<ExcelSheet> listExcelSheetsResult)
        {
            _Workbook workBook;
            object[,] excelSheetResult = new object[65540, GlobalConstants.maxColumns];
            try
            {
                // creating new WorkBook within Excel application
                workBook = excelApp.Workbooks.Add(Type.Missing);
            }
            catch (Exception e)
            {
                // debug
                MessageBox.Show("Cannot save file, please ensure that Excel is not opening else where.\n"+e.Message, "Save file error");
                return;
            }

            _Worksheet xlSheet = workBook.Sheets[1] as _Worksheet;
            xlSheet = workBook.ActiveSheet as _Worksheet;
            // store header part in Excel
            for (int j = 0; j < listAllPagesCompare[0].listColumnCompare.Count; j++)
                xlSheet.Cells[1, j + 1] = listAllPagesCompare[0].listColumnCompare[j].fieldName;
            
            int lastCellColumn = listAllPagesCompare[0].listColumnCompare.Count + GlobalConstants.ExtendColumn;
            // int lastCellColumn = GlobalVariables.lastColumn + GlobalConstants.ExtendColumn;

            xlSheet.Cells[1, lastCellColumn] = GlobalConstants.ExtendColumnName;

            long bookmarkRow = 0;

            for (int i = GlobalVariables.startSheet; i <= numsheets; i++)
            {
                int startRow = listAllPagesCompare[i].startRow;
                int endRow = listAllPagesCompare[i].endRow;
                for (int k = 0; k < lastCellColumn; k++)
                {
                    for (int j = 0; j <= (endRow - startRow); j++)
                    {
                        excelSheetResult[bookmarkRow + j, k] = listExcelSheetsResult[i].value[j, k];
                    }
                }
                if (endRow >= startRow)
                    bookmarkRow = bookmarkRow + (endRow - startRow + 1);
            }
            //store data to excel
            string lastCell = GetLastCellName(lastCellColumn, bookmarkRow);
            xlSheet.get_Range("A2", lastCell).NumberFormat = "@";
            xlSheet.get_Range("A2", lastCell).Value2 = excelSheetResult;
            
            try
            {
                // delete destination file, if any
                if (File.Exists(excelFileName))
                    File.Delete(excelFileName);

                // save the workbook
                workBook.SaveAs(excelFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing);

                // set curent status of excel file to "just saved"
                saved = true;
            }
            catch (Exception e)
            {
                // debug
                MessageBox.Show("File cannot be saved, please check file permissions.\n" + e.Message , "Save file error");
            }
            finally
            {
                // close workbook and worksheet
                excelSheetResult = null;
                workBook.Close();
                Marshal.ReleaseComObject(workBook);
                workBook = null;
                GC.Collect();
            }
        }

        public static string GetLastCellName(int maxColumn, long maxRow)
        {
            string col = ((string)(Convert.ToChar(maxColumn / 26 + 64).ToString() + Convert.ToChar(maxColumn % 26 + 64))).Replace('@', ' ').Trim();
            return String.Concat(col, (maxRow + 1).ToString());
        }

        public void SaveFilePathToRegistry()
        {
            if (String.IsNullOrEmpty(excelFileName))
                RegistryService.SetValue(GlobalConstants.REGISTRY_LAST_EXCEL_KEY, GlobalConstants.REGISTRY_LAST_EXCEL_NULL_VALUE);
            else
            // save destination file path to registry
                RegistryService.SetValue(GlobalConstants.REGISTRY_LAST_EXCEL_KEY, excelFileName);
        }

        /// <summary>
        /// recover an autosaved file to a user's defined destionation file
        /// by copy autosaved file to destionation file
        /// </summary>
        public void Recover()
        {
            SaveFileDialog saveFileDialogRecover = new SaveFileDialog();
            saveFileDialogRecover.Filter = "Excel|*.xls";
            saveFileDialogRecover.Title = "Recover File";
            DialogResult result = saveFileDialogRecover.ShowDialog();

            if (result == DialogResult.OK && !String.IsNullOrEmpty(saveFileDialogRecover.FileName))
            {
                try
                {
                    // try to copy autosaved file to new destination
                    File.Copy(excelFileName, saveFileDialogRecover.FileName, true);

                    // inform user if succeeds
                    MessageBox.Show(
                        "Recovered file saved at \"" + saveFileDialogRecover.FileName + "\" successfully!",
                        "Recovery succeeds", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                catch 
                {
                    // in case of recovery failure, inform user and ask for retrying
                    DialogResult dialogResult = MessageBox.Show(
                                                                "Cannot save file to your choosen path, please check file permissions." +
                                                                "\nAutosaved file: " + excelFileName +
                                                                "\nDestination file: " + saveFileDialogRecover.FileName +
                                                                "\n\nDo you want to try again?",
                        "Recovery fails", MessageBoxButtons.RetryCancel, MessageBoxIcon.Error);

                    // retry if user chooses so
                    if (dialogResult == DialogResult.Retry)
                        Recover();
                }
            }
            
            // delete autosaved file
            File.Delete(excelFileName);
        }

        /// <summary>
        /// save a copy of current excel file to a user's defined destionation file
        /// </summary>
        public void SaveACopy()
        {
            SaveFileDialog saveFileDialogRecover = new SaveFileDialog();
            saveFileDialogRecover.Filter = "Excel|*.xls";
            saveFileDialogRecover.Title = "Save a Copy";
            DialogResult result = saveFileDialogRecover.ShowDialog();

            if (result == DialogResult.OK && !String.IsNullOrEmpty(saveFileDialogRecover.FileName))
            {
                try
                {
                    System.IO.File.Copy(excelFileName, saveFileDialogRecover.FileName, true);
                }
                catch (Exception e)
                {
                    MessageBox.Show(
                        "Cannot save file to your choosen path, please check destination file permission.",
                        "Error while saving.\n" + e.Message, MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }

        public void QuitExcelApp()
        {
            excelApp.Quit();
            /* Hoang_nguyenHuu Added */
            GC.Collect();
        }

        public bool ImportDataFromExcelFileToCompare(out List<ExcelSheet> listExcelSheets)
        {
            Workbook workBook;
            Worksheet workSheet;
            List<ExcelSheet> listSheets = new List<ExcelSheet>();
            // eliminate Zero base array, change to 1 base array
            listExcelSheets = null;
            try
            {
                workBook = excelApp.Workbooks.Open(this.FileName, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                int numSheets = workBook.Sheets.Count;
                this.sheetsCount = numSheets;
                if (GlobalVariables.startSheet > numSheets)
                {
                    GlobalVariables.startSheet = numSheets;
                }
                // init Data for listSheets
                for (int i = 0; i <= numSheets; i++)
                    listSheets.Add(new ExcelSheet());
                
                for (int i = GlobalVariables.startSheet; i < numSheets + 1; i++)
                { 
                    workSheet = (Worksheet)workBook.Sheets[i];
                    //
                    // Take the used range of the sheet. Finally, get an object array of all
                    // of the cells in the sheet (their values). You can do things with those
                    // values. 
                    
                    // Copy data from Excel to Array
                    string lastCell = GetLastCellName(GlobalVariables.lastColumn, GlobalConstants.maxRows);
                    // Range excelRange = workSheet.get_Range("A1",last);
                    Range excelRange = workSheet.get_Range("A1", lastCell);                    
                    ExcelSheet Sheet = new ExcelSheet((object[,])excelRange.get_Value(
                        XlRangeValueDataType.xlRangeValueDefault));

                    // Identify the last row of Excel sheet for compare
                    if (FormMergeTool.listAllPagesCompare[i].endRow == 0) // XML has no defination about endRow
                    {
                        Range last = workSheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing);
                        // check last row again
                        int r = last.Row;
                        /*bool blastRow = false;
                        //  Remove all blank row
                        for (r = last.Row; r >= 1; r--)
                        {
                        for (int c = 0; c < FormCompareTool.listAllPagesCompare[1].listColumnCompare.Count; c++)
                        {
                        int col = FormCompareTool.listAllPagesCompare[1].listColumnCompare[c].column;
                                
                        if ((Sheet.value[r, col] != null) && ((string)Sheet.value[r, col] != string.Empty))
                        {
                        blastRow = true;
                        break;
                        }
                        }
                        if (blastRow) break;
                        }*/
                        FormMergeTool.listAllPagesCompare[i].endRow = r;
                    }
                    
                    listSheets[i] = Sheet;                    
                }
                // Dispose Excel
                workBook.Close();
                workSheet = null;
                workBook = null;
                GC.Collect();
                //
                listExcelSheets = listSheets;
                if (listExcelSheets != null)
                    return true;
                else
                    return false;
            }
            catch (Exception e)
            {
                MessageBox.Show(
                    "Cannot open excel file, please ensure the file is not used by other applications.\n"+ e.Message,
                    "Error while opening file.\n" + e.Message, MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }

        public void ImportDataFromExcelFile(String fileName, ref DataGridView dataGridView)
        {
            Workbook workBook;
            Worksheet workSheet;

            // open destination workbook file
            try
            {
                workBook = excelApp.Workbooks.Open(fileName, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                workSheet = (Worksheet)workBook.ActiveSheet;
            }
            catch ( Exception e)
            {
                MessageBox.Show(
                    "Cannot open excel file, please ensure the file is not used by other applications.\n"+ e.Message,
                    "Error while opening file.\n" + e.Message, MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            // get data from excel to temp array
            // single line mode
            string lastCellName;
            if (GlobalConstants.TemplatePageCol == 0)
                lastCellName = GetLastCellName(dataGridView.Columns.Count, dataGridView.Rows.Count);
            //multiline mode
            else
            {
                Range lastCell = workSheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing);
                int rowIndex = lastCell.Row - 1;
                lastCellName = GetLastCellName(dataGridView.Columns.Count, rowIndex);

                // add more rows to datagridview to meet excel's rows count
                if (rowIndex >= dataGridView.Rows.Count)
                    dataGridView.Rows.Add(rowIndex - dataGridView.Rows.Count);
            }

            object[,] values = workSheet.get_Range("A2", lastCellName).Value2 as object[,];                        
            // copy array to data grid view
            
            for (int i = 0; i < dataGridView.Rows.Count; i++)
                for (int j = 0; j < dataGridView.Columns.Count; j++)
                {
                    if (GlobalConstants.IsDateColumn(j))
                    {
                        double d;
                        if (values[i + 1, j + 1] != null && Double.TryParse(values[i + 1, j + 1].ToString(), out d))
                            dataGridView.Rows[i].Cells[j].Value = DateTime.FromOADate(d);
                        else
                            dataGridView.Rows[i].Cells[j].Value = values[i + 1, j + 1];
                    }
                    else
                    {
                        dataGridView.Rows[i].Cells[j].Value = values[i + 1, j + 1];
                    }
                }

            // close workbook and worksheet
            workBook.Close();
            workSheet = null;
            workBook = null;
            GC.Collect();
        }
    }
}
